'''
UNIT INFO
DB_species is the place where differences between database-types
are specified.

SAMPLE DATATYPES
pysqlite:
{'MsNumber': 'REAL', 'Remark': 'TEXT', 'RelID': 'INTEGER', 'Source': 'TEXT', 'Selected': 'INTEGER', 'MsDate': 'DATE', 'Object1': 'TEXT', 'Object2': 'TEXT', 'Relation': 'TEXT', 'ModDate': 'DATE', 'MsCurrency': 'REAL', 'MsLongText': 'TEXT', 'Change': 'TEXT', 'Subject': 'TEXT'}
access pyodbc:
{'MsNumber': "<type 'float'>", 'Remark': "<type 'unicode'>", 'RelID': "<type 'int'>", 'Source': "<type 'unicode'>", 'Selected': "<type 'bool'>", 'MsDate': "<type 'datetime.datetime'>", 'Object1': "<type 'unicode'>", 'Object2': "<type 'unicode'>", 'Relation': "<type 'unicode'>", 'ModDate': "<type 'datetime.datetime'>", 'MsCurrency': "<type 'float'>", 'MsLongText': "<type 'unicode'>", 'Change': "<type 'unicode'>", 'Subject': "<type 'unicode'>"}

ADAP HIS
- modules to make work with sqlite
    -lisco
    -genframes (non db)
    -treeco
    -other functions
        -branche-queries
        -exchange stuff

-calendar-stuff
    -unicode-to-string-conversion fails in lisco...writelistitems
        -is this conversion really necessary? maybe not but:
            -for now create a type-based mapping
-database adaps:
    -bring over the view-change to knobjex-sqlite
    -add test-data to this db
    -update the other views
-jodb
    -wildcard is already %
    -getdatatypelist or -dict
        v-check current code
        v-move code to db_species
            -not very happy with callback to jodb
            -test
        v-test pragmas with pysqlite
        v-fill in pragma
            v-datatypes are different; see read/write forms
-update auiframework
    -unparametrized part
        -findable thru readFromSql and writeFromSql
            -make query for stuff in readObjects
    -adap read/write forms
        -split code for db-types and handle unicode
            -readOption
            -updateOption
            -readRecord
                -check total reading procedure
                    -readobjects
                        -history
                            -set default date-values in code not in base
                            -split for dbtypes (temp.)
            -writeRecord
-update queries; link daynames to dayids

ADAP NO


ADAP FUT
- introduce generalized datatype-set, type-mapping and conversion-types
(see temp-task-tree.doc)
'''

import sys
import pyodbc
from pysqlite2 import dbapi2 as sqlite
##import jodb
import pardb

# must be overridden from calling app

##curDatabase = 'KnobjexODBC'
# access database:
##db_typest = 'ms access 2000'
##curDatabase = 'KnobjexProductie'
# ----future?-----api_typest = 'pyodbc'

# sqlite database:
db_typest = 'sqlite3'
##curDatabase = ''
##curDatabase = 'M:\\EigThuis\\Bestanden\\Planning en Beheer\\Processen en kennis\\kb_sqlite_eigen\\eigen_knobjex.db'
curDatabase = '/media/truecrypt1/EigThuis/Bestanden/Leuk/Hobbies/Programmeren/Python/MijnCode/PyLinuxKnobjex/kbx_python_files/database/knobjex_sqlite.db'
##curDatabase = 'M:\\EigThuis\\Bestanden\\Leuk\\Hobbies\\Programmeren\\Python\\MijnCode\\PyKnow\\database\\knobjex_sqlite.db'
##'M:/EigThuis/Bestanden/Leuk/Hobbies/Programmeren/sqlite_dbs/knobjex_test.db'
# ---future?-----api_typest = 'pysqlite'

settingsdbst = ''


def cursorFromDB(sqlst, writebo, dbst = ''):
    '''Create and execute a cursor from the sql-string'''
    if dbst == '': dbst = curDatabase
    if db_typest == 'ms access 2000':
        cnx = pyodbc.connect('dsn=' + dbst + ';UID=Admin;PWD=''')
    elif db_typest == 'sqlite3':
        cnx = sqlite.connect(dbst)
        cnx.text_factory = str      # remove when upgrading to unicode

    cur = cnx.cursor()
    cur.execute(sqlst)
    if writebo: cnx.commit()      # necessary fot writes to database
    return cur


def getDataTypeListDB(tablest, dbst=''):
    if db_typest == 'ms access 2000':
        cursor = pardb.cursorFromSql(curDatabase, pardb.sqlFromParams(tablest))
        descriptu2 = cursor.description
        fieldTypeli = [str(t) for n,t,d,i,p,s,u in descriptu2]
        cursor.close()
        return fieldTypeli
    elif db_typest == 'sqlite3':
        queryst = 'pragma table_info(' + tablest + ')'
##        print queryst
        tabinfocur = pardb.readFromSql(curDatabase, queryst)
        fieldTypeli = [str(ftype) for pos, fname, ftype, x, y, z in tabinfocur]
        return fieldTypeli


def getDataTypeDictDB(tablest, dbst=''):
    if db_typest == 'ms access 2000':

        cursor = pardb.cursorFromSql(curDatabase, pardb.sqlFromParams(tablest))
        descriptu2 = cursor.description
        fieldTypedi={}
        fieldTypeli = [str(t) for n,t,d,i,p,s,u in descriptu2]
        fieldNameli = [str(n) for n,t,d,i,p,s,u in descriptu2]
        n= 0
        for elem in fieldNameli:
            fieldTypedi[elem]= fieldTypeli[n]
            n=n+1
        cursor.close()
    ##    for n,t  in descriptu2:
    ##        fieldTypedi[str(n)] = str(t)
        return fieldTypedi
    elif db_typest == 'sqlite3':
        queryst = 'pragma table_info(' + tablest + ')'
        tabinfocur = pardb.readFromSql(curDatabase,  queryst)
        fieldTypedi={}
        for row in tabinfocur:
            fieldTypedi[str(row[1])]= str(row[2])
        return fieldTypedi


def dateSeparator():
    if db_typest == 'ms access 2000':
        return "#"
    elif db_typest == 'sqlite3':
        return "'"


def testConnectSqlite(filepathst):
    '''
    test connection with (correct) sqlite-database-file
    '''

    connectionresultst = ''
    connectbo = False
    try:
        cnx = sqlite.connect(filepathst)
        cur = cnx.cursor()
        sqlst = 'SELECT * FROM PATHWAYS'
        cur.execute(sqlst)
    except:
        connectionresultst =  str(sys.exc_info()[1])  + '\n' + \
                            str(sys.exc_info()[0])
        print connectionresultst
        connectbo = False
    else:
        connectionresultst = 'OK'
        connectbo = True
        cur.close()

    return connectbo
##    return connectionresultst



if __name__ == '__main__':
##    pathst = 'M:\EigThuis\Bestanden\Leuk\Hobbies\Programmeren\Python\MijnCode\PyKnow\database\knobjex_sqlite.db'
    pathst = 'M:\\EigThuis\\Bestanden\\Leuk\\Hobbies\\Programmeren\\Python\\MijnCode\\PyKnow\\database\\testdeze.db'
##    pathst = 'M:\EigThuis\Bestanden\Leuk\Hobbies\Programmeren\Python\MijnCode\PyKnow\database\testdeze.db'
##    pathst = 'wrongpath'
    print testConnectSqlite(pathst)

##    print getDataTypeDictDB('Relations')
